﻿ 

if  exists (select * from sys.database_principals where name = N'ContentBasicAccess' and type = 'r')
begin
	exec sp_droprolemember @rolename =  'ContentBasicAccess', @membername = 'ContentDefaultUser';
	drop role [ContentBasicAccess];
end;
create role [ContentBasicAccess] authorization [db_owner];
GO



if  exists (select * from sys.server_principals where name = N'ContentDefaultUser')
	drop login [ContentDefaultUser];
create login [ContentDefaultUser] with password = 'pwd-123789', default_database = [Content], check_policy = off;
GO

if  exists (select * from sys.database_principals where name = N'ContentDefaultUser')
	drop user [ContenttDefaultUser];
create user [ContentDefaultUser] for login [ContentDefaultUser] with default_schema = [Content];
GO


exec sp_addrolemember @rolename = N'ContentBasicAccess', @membername = N'ContentDefaultUser';
GO


declare @roleName varchar(30);
select @roleName = 'ContentBasicAccess';


declare @sql nvarchar(3000);		
select @sql = N'grant select, insert, update, delete on Content.Content to ' + @roleName + ';';
select @sql = @sql + N'grant select, insert, update, delete on Content.ContentFile to ' + @roleName + ';';
select @sql = @sql + N'grant select, insert, update, delete on Content.MediaType to ' + @roleName + ';';
select @sql = @sql + N'grant select, insert, update, delete on Content.rAllowedContentType to ' + @roleName + ';';
select @sql = @sql + N'grant select, insert, update, delete on Content.rAllowedMediaType to ' + @roleName + ';';
select @sql = @sql + N'grant select, insert, update, delete on Content.Storage to ' + @roleName + ';';
select @sql = @sql + N'grant select, insert, update, delete on dbo.ContentQuality to ' + @roleName + ';';
select @sql = @sql + N'grant select, insert, update, delete on dbo.ContentType to ' + @roleName + ';';
exec sp_executesql @sql;